package com.onlinevideo.util;


import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SQLUtils {
    /**
     *
     * 使用该方法的条件：
     * 1.查询语句的全字段例如-》SELECT `id` , `username`, `password`, `logo`, `user_type` AS  `userType`, `user_status` AS  `userStatus`, `user_validity` AS  `userValidity`
     * FROM `t_user` WHERE 1=1 AND  `id`= ? AND `username`=? AND `password`=? AND `logo`=? AND `user_type`=? AND `user_status`=? AND `user_validity` = ?
     * 2.1=1必须添加。
     * 3.1=1后的条件语句中的数据库字段顺序必须和实体层类中的属性顺序一致。
     * 注意事项：
     * 1.当前仅允许使用基本数据类型的封装类（实体层中属性的数据类型为基本数据类型的封装类），其他的未匹配使用
     * 作用：
     * 在条件查询的时候因dao层需要书写大量的判空和字符拼接，此方法为简化该步骤，仅需写出完整的全条件查询语句，以及携带数据的对象T(泛型)，如果该数据中某些属性值为null则会移除该部分sql拼接字段。
     * 返回值：
     * 处理完成后的sql语句，以及条件语句对应的数据。
     * @param sql 查询语句的全字段查询语句
     * @param t 携带的数据（为实体对象）
     * @param <T> 参数类型（）
     * @return map（sql：裁剪后字符串；data：查询条件数据）
     */
    public static <T>Map sqlValueMapping(String sql , T t)  {


        List<Object> val = new ArrayList<>();
        Map<String,Object> kv = new HashMap<>();
        StringBuffer sb = new StringBuffer(sql);
        if(t==null){
            int index = String.valueOf(sb).toUpperCase().indexOf("AND");
            sb = sb.delete(index, sb.length());
            kv.put("sql",String.valueOf(sb));
            kv.put("data",val.toArray());
            return kv;
        }
        Class cas = t.getClass();
        Field[] fields = cas.getDeclaredFields();
        int index = String.valueOf(sb).toUpperCase().indexOf("AND");
        for(int i = 0 ; i<fields.length;i++){
            char[] cs=fields[i].getName().toCharArray();
            if(cs[0]>90){cs[0]-=32;}
            Method method = null;
            try {
                Field field;
                method = cas.getMethod("get"+String.valueOf(cs));
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            }
            Object invoke = null;
            try {
                invoke = method.invoke(t);
            } catch (InvocationTargetException | IllegalAccessException e) {
                e.printStackTrace();
            }
            int end = String.valueOf(sb).toUpperCase().indexOf("AND",index+1);
            if(invoke==null){
                if(end !=-1){
                    sb = sb.delete(index, end);
                }else {
                    sb = sb.delete(index, sb.length());
                }
            }else {
                index = end ;
                val.add(invoke);
            }
        }
        kv.put("sql",String.valueOf(sb));
        kv.put("data",val.toArray());
        return kv;
    }
    public static<T> String CreateInsertSql(Class<T> tClass)  {
        StringBuilder insert = new StringBuilder("INSERT INTO ");
        char[] chars = tClass.getSimpleName().toCharArray();
        chars[0] += 32;
        insert.append("`T_").append(chars).append("` (`");
        Field[] declaredFields = tClass.getDeclaredFields();
        for (Field f : declaredFields) {
            String fName = f.getName();
            if ("id".equals(fName)){
                continue;
            }
            StringBuilder stringBuilder = new StringBuilder(fName);
            stringBuilder = getName(stringBuilder);
            insert.append(stringBuilder).append("`").append(",`");
        }
        insert.deleteCharAt(insert.length()-1).deleteCharAt(insert.length()-1);
        insert.append(") VALUES (");
        for (int i = 0; i < declaredFields.length-1; i++) {
            insert.append("?,");
        }
        insert.deleteCharAt(insert.length()-1).append(")");
        return insert.toString();
    }
    public static<T> String CreateUpdateSql(Class<T> tClass)  {
        StringBuilder insert = new StringBuilder("UPDATE ");
        char[] chars = tClass.getSimpleName().toCharArray();
        chars[0] += 32;
        insert.append("`T_").append(chars).append("` SET `");
        Field[] declaredFields = tClass.getDeclaredFields();
        for (Field f : declaredFields) {
            String fName = f.getName();
            if ("id".equals(fName)){
                continue;
            }
            StringBuilder stringBuilder = new StringBuilder(fName);
            for (int i = 0; i < stringBuilder.length(); i++) {
                char c = stringBuilder.charAt(i);
                if (c < 'a') {
                    stringBuilder.replace(i, i + 1, "_" + (char) ((c) + 32));
                }
            }
            insert.append(stringBuilder).append("`=?,`");
        }
        insert.deleteCharAt(insert.length()-1).deleteCharAt(insert.length()-1).append(" WHERE `id`=?");
        return insert.toString();
    }
    public static<T> String CreateQuerySql(Class<T> tClass)  {
        StringBuilder insert = new StringBuilder("SELECT ");
        Field[] declaredFields = tClass.getDeclaredFields();
        for (Field f : declaredFields) {
            //别名
            String fName = f.getName();
            boolean flag = false;
            StringBuilder stringBuilder = new StringBuilder(fName);
            for (int i = 0; i < stringBuilder.length(); i++) {
                char c = stringBuilder.charAt(i);
                if (c < 'a') {
                    stringBuilder.replace(i, i + 1, "_" + (char) ((c) + 32));
                    flag = true;
                }
            }
            if (flag){
                insert.append("`").append(stringBuilder).append("` AS ");
            }
            insert.append("`").append(fName).append("`,");
        }
        insert.deleteCharAt(insert.length()-1).append(" FROM ");
        char[] chars = tClass.getSimpleName().toCharArray();
        chars[0] += 32;
        insert.append("`T_").append(chars).append("` WHERE 1=1 ");
        for (Field f : declaredFields) {
            String fName = f.getName();
            StringBuilder stringBuilder = new StringBuilder(fName);
            stringBuilder = getName(stringBuilder);
            insert.append("AND ").append("`").append(stringBuilder).append("`=? ");
        }
        return insert.toString();
    }

    public static<T> StringBuilder getName(StringBuilder stringBuilder)  {
        for (int i = 0; i < stringBuilder.length(); i++) {
            char c = stringBuilder.charAt(i);
            if (c < 'a') {
                stringBuilder.replace(i, i + 1, "_" + (char) ((c) + 32));
            }
        }
        return stringBuilder;
    }

    public static<T> String getTableName(Class<T> tClass)  {
        StringBuilder stringBuilder = new StringBuilder(tClass.getSimpleName());
        for (int i = 0; i < stringBuilder.length(); i++) {
            char c = stringBuilder.charAt(i);
            if (c < 'a') {
                stringBuilder.replace(i, i + 1, "_" + (char) ((c) + 32));
            }
        }
        return "T" + stringBuilder.toString();
    }

}
